from openpyxl import load_workbook,Workbook
file_path = 'data/template/university/D00.xlsx'
from models import File, User, Subject, Point
from app import db
import os
import pandas as pd
import numpy as np
from flask import request, jsonify
import time
from flask_login import current_user

df = pd.read_csv('subject.txt', sep=',')
print(df.columns)
# print(df)

def mkdir_subject(base_dir, subject_df):
    subjects = subject_df['学科'].tolist()
    # print(subjects)
    for sub in subjects:
        os.mkdir(os.path.join(base_dir, sub))

base_dir = 'data/fill_data/subject/'
# mkdir_subject(base_dir, df)

# 将所有的一流大学指标，和一流学科指标入库
def insert_subjects(df):
    print(df.columns)
    a = df['代码'].tolist()
    b = df['学科'].tolist()
    print(a)
    print(b)
    temp = []
    for index, code in enumerate(a):
        user_id = np.random.randint(0, 6, 1)[0]
        s = Subject(code=code, name=b[index], user_id=user_id)
        temp.append(s)
    print(temp)
    db.session.add_all(temp)
    db.session.commit()

# insert_subjects(df
from models import Subject
import json

# 加载所有学科
def view_subject_index():
    subjects = Subject.query.all()
    data = []
    for s in subjects:
        temp = {}
        temp['id'] = s.id
        temp['subject'] = s.name # 学科数据
        temp['username'] = s.user.name
        temp['process'] = '' # 进度需要查看文件保存表
        temp['update_time'] = '' # 学科修改时间，需要查看文件保存表
        temp['record'] = '' # 历史记录
        data.append(temp)
    return json.dumps({"data":data}, ensure_ascii=False)

# print(view_subject_index())

# 将所有的
def insert_points(path):
    files = []
    for item in os.listdir(path):
        if not item.startswith('~$'):
            print(os.path.join(path, item))
            files.append(os.path.join(path, item))
    data = []
    if 'university' in path:
        point_type = 'D'
    elif 'subject' in path:
        point_type = 'X'
    else:
        point_type = ''
    for file in files:
        df = pd.read_excel(file, sheet_name=0)
        project = df['监测项目'].dropna(how='any').tolist()[0]
        factors = df['监测要素'].dropna(how='any').tolist()
        points = df['核心监测点'].dropna(how='any').tolist()
        point2factor = {}
        for point in points:
            for factor in factors:
                if point.startswith(factor[:5]):
                    point2factor[point] = factor
                    break
        contents = df['监测点说明'].fillna(value='')
        explains =  df['说明'].fillna(value='')
        temp = []
        for index, point in enumerate(points):
            p = Point(id= point[:7],
                      project=project,
                      factor=point2factor[point],
                      point=point,
                      content=contents[index],
                      explain=explains[index],
                      point_type=point_type)
            temp.append(p)
        db.session.add_all(temp)
        db.session.commit()


insert_points('./data/template/university/') # 插入大学监测点
insert_points('./data/template/subject/') # 插入学科监测点

# 生成所有的模板
def build_all_templates():
    path = 'data/template/subject/'
    print('university' in path)

# build_all_templates()

def upload_point_file(point_id):
    """
    """
    f = request.files['file']
    sub = request.form.get('subject') # “大学”, "世界史“, ”中国史“
    if f :
        if point_id.startswith('D'):
            file_path = 'data/fill_data/university/'
        elif point_id.startswith('X'):
            file_path = 'data/fill_data/subject/'+str(sub)
        else:
            return jsonify({'status_code':404, "reason":"文件名称或者格式有问题"})
        now = int(time.time())
        timeArray = time.localtime(now)
        upload_time = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
        save_path = os.path.join(file_path, f.filename)
        f.save(save_path)
        # 写入数据库
        try:
            new_file = File(name=f.filename,
                            path=save_path,
                            file_type=sub,
                            status='已上传',
                            user_id=current_user.id,
                            upload_time=upload_time)
            db.session.add(new_file)
            db.session.commit()
        except Exception as e:
            print(e)
            db.session.rollback()
        return jsonify({'status_code':200, 'info':'success'})
    else:
        return jsonify({'status_code':404, 'info':'文件格式或名称错误'})

def view_uni_index():
    points = Point.query.filter_by(point_type='D')
    res = []
    for point in points:
        data_json = {}
        data_json['id'] = point.id
        data_json['project'] = point.project
        data_json['factor'] = point.factor
        data_json['point'] = point.point
        data_json['content'] = point.content
        data_json['explain'] = point.explain
        data_json['user'] = point.user.name if point.user else "未分配"
        data_json['state'] = 1 # 状态
        data_json['record'] = ['无', '无', '无']
        res.append(data_json)
    return json.dumps({'data':res}, ensure_ascii=False)

print(view_uni_index())

def view_subject_index():
    points = Point.query.filter_by(point_type='X')
    res = []
    for point in points:
        data_json = {}
        data_json['id'] = point.id
        data_json['project'] = point.project
        data_json['factor'] = point.factor
        data_json['point'] = point.point
        data_json['content'] = point.content
        data_json['explain'] = point.explain
        data_json['user'] = point.user.name if point.user else "未分配"
        data_json['state'] = 1 # 状态
        data_json['record'] = ['无', '无', '无']
        res.append(data_json)
    return json.dumps({'data':res}, ensure_ascii=False)

print(view_subject_index())

# def build_all_points_template():
#     """
#     生成所有的样表
#     :return:
#     """
#     pass


